<?php
// 本类由系统自动生成，仅供测试用途
namespace Admin\Controller;
use Think\Controller;
class StatisticsController extends Controller {
    /**
     * 导出Excel
     */
    public  function StatisticsChannelExcel(){
            // $meid = $this->Islogin();
            $meid = 7;
            $admin_mdb =  M('admin');
            $wayuser_mdb =  M('wayuser');
            $city_db = D('city');
            $medata = $admin_mdb->where('id='.$meid)->find();
            switch ($medata['position']) {
                // 全国权限
                case '1100':
                    $where_city = '';
                    if(!empty(I('get.province'))){
                        $where_city = ' and provinceid='.I('get.province');
                    }
                    $iscs = false;
                    // $where_city = ' and provinceid=1';
                    // 获取城市列表供选择
                    $response['provinceid'] = $city_db->CityGetAll(false,false,false);
                    $response['position'] = 1100;
                    break;
                // 省总权限
                case '4':
                    $where_city = ' and provinceid='.$medata['provinceid'];
                    $iscs = false;
                    $response['position'] = 4;
                    break;
                // 城市经理
                case '3':
                    $where_city = ' and pid='.$medata['id'];
                    $iscs = true;
                    $response['position'] = 3;
                    break;
                default:
                    exit(json_encode(['error'=>'1','message'=>'您的账号权限不涉及本功能！']));
            }
            $BeginDate = strtotime(date("Y-m-01"));
            $EndDate = mktime(0, 0, 0,date('m')+1,1)-1;
            $Beginday = strtotime($geta['starttime'].' 00:00:00');
            $EndDay = strtotime($geta['endtime'].' 23:59:59');
            
            // $timesql = ' and create_time>='.$Beginday.' and create_time<='.$EndDay;
            $timesql = '';
            // 所有城市经理
            $manager = $admin_mdb->where('position=3'.$where_city.$this->istest['testsql'])->select();
            $channelcount = [];
            // 城市经理总数
            $cscount = count($manager);
            $gwcount = 0;
            $data = [];
            // 渠道状态
            $status_arr = ['0'=>'等待城市经理审核','1'=>'等待风控审核','2'=>'已激活','3'=>'关闭状态（已通过审核）','-1'=>'被拒绝','-2'=>'被驳回（正在修改）'];
            for ($i=0; $i <  $cscount; $i++) { 
                // 城市经理下属的所有GW
                $Gwdata = $admin_mdb->where('pid='.$manager[$i]['id'].' and position=1'.$this->istest['testsql'])->field('id,name,tel')->select();
                $manager_sheet_title[] = $manager[$i]['name'];
                $data[$i] = [];
                // 统计渠道数据
                if(!$Gwdata){
                    $manager[$i]['channelcount'] = 0;
                    $manager[$i]['channel_create_count'] = 0;
                    $manager[$i]['channel_success_count'] = 0;
                    $manager[$i]['channel_refuse_count'] = 0;
                    $manager[$i]['channel_back_count'] = 0;
                    $manager[$i]['GWcount'] = 0;
                    $manager[$i]['GWlist'] = [];
                }else{
                    $manager[$i]['GWcount'] = count($Gwdata);
                    $Gwdataids = implode(',',array_column($Gwdata,'id'));
                    $manager[$i]['channelcount'] = $wayuser_mdb->where('admin_id in('.$Gwdataids.')'.$timesql)->count();
                    $data[$i][] = ['顾问姓名','','','','',''];
                    for ($s=0; $s < count($Gwdata); $s++) { 
                        // 总数
                        $Gwdata[$s]['channel_count'] = $wayuser_mdb->where('admin_id='.$Gwdata[$s]['id'].$timesql)->count();
                        // 待审
                        $manager[$i]['channel_create_count'] += $Gwdata[$s]['channel_create_count'] = $wayuser_mdb->where('status>=0 and status<=1 and admin_id='.$Gwdata[$s]['id'].$timesql)->count();
                        // 通过
                        $manager[$i]['channel_success_count'] += $Gwdata[$s]['channel_success_count'] = $wayuser_mdb->where('status=2 and admin_id='.$Gwdata[$s]['id'].$timesql)->count();
                        // 被拒
                        $manager[$i]['channel_refuse_count'] += $Gwdata[$s]['channel_refuse_count'] = $wayuser_mdb->where('status=-1 and admin_id='.$Gwdata[$s]['id'].$timesql)->count();
                        // 被驳回
                        $manager[$i]['channel_back_count'] += $Gwdata[$s]['channel_back_count'] = $wayuser_mdb->where('status=-2 and admin_id='.$Gwdata[$s]['id'].$timesql)->count();
                        $data[$i][] = ['','','','','',''];
                        // 表头（顾问单位）
                        $data[$i][] = [
                            $Gwdata[$s]['name'],
                            '姓名',
                            '手机号',
                            '工作单位',
                            '推荐码',
                            '状态'
                        ];
                        $data[$i][] = [
                            '按顾问“'.$Gwdata[$s]['name'].'”统计',
                            '渠道总数：'.$Gwdata[$s]['channel_count'],
                            '审核中渠道：'.$Gwdata[$s]['channel_create_count'],
                            '已通过渠道：'.$Gwdata[$s]['channel_success_count'],
                            '被拒绝渠道：'.$Gwdata[$s]['channel_refuse_count'],
                            '被驳回渠道：'.$Gwdata[$s]['channel_back_count']
                        ];
                        $waydata = $wayuser_mdb->where('admin_id='.$Gwdata[$s]['id'].$timesql)->select();
                        for ($j=0; $j < $Gwdata[$s]['channel_count']; $j++) { 
                            $data[$i][] = [
                                $Gwdata[$s]['name'],
                                $waydata[$j]['name'],
                                $waydata[$j]['tel'],
                                $waydata[$j]['company'],
                                $waydata[$j]['waynum'],
                                $status_arr[$waydata[$j]['status']]
                            ];
                        }
                    }
                }
                // 表头（城市经理单位）
                $manager_title[$i] = ['按城市经理统计',
                  '渠道总数：'.$manager[$i]['channelcount'],
                  '审核中渠道：'.$manager[$i]['channel_create_count'],
                  '已通过渠道：'.$manager[$i]['channel_success_count'],
                  '被拒绝渠道：'.$manager[$i]['channel_refuse_count'],
                  '被驳回渠道：'.$manager[$i]['channel_back_count']
                ];
            }
            if(I('get.province') == 1){
                $filename_n = '截止'.date('Y-m-d').'渠道数据(CQ)';
            }else if(I('get.province') == 3){
                $filename_n = '截止'.date('Y-m-d').'渠道数据(SC)';
            }else{
                $filename_n = '截止'.date('Y-m-d').'渠道数据(全国)';
            }
            
            // 生成Excel并返回文件名称
            // print_r($manager_title);
            // print_r($data);
            // print_r($manager_sheet_title);
            // print_r($filename_n);die;
            $filename = ExportExcelTable($manager_title,$data,$manager_sheet_title,$filename_n,'./Public/Uploads/ChannelCount/');
            exit(json_encode(['success'=>'OK','filename'=>$filename_n,'message'=>$filename_n.'导出成功！']));
    }

    
    /**
     * 统计各渠道对应的用户数量
     * 生成的Excel文件将存放在 Public/Uploads/ChannelCount 下，并以当天日期命名
     */
    public function CountChannelUser() 
    {
        // $meid = $this->Islogin();
        $meid = 7;
        $admin_mdb =  M('admin');
        $wayuser_mdb =  M('wayuser');
        $user_mdb =  M('user');
        $city_db = D('city');
        $geta = I('get.');
        $medata = $admin_mdb->where('id='.$meid)->find();
        switch ($medata['position']) {
            // 全国权限账号
            case '1100':
                $where_city = '';
                if(!empty(I('get.province'))){
                    $where_city = ' and provinceid='.I('get.province');
                }
                $response['provinceid'] = $city_db->CityGetAll(false,false,false);
                $iscs = false;
                $response['position'] = 1100;
                break;
            // 省总权限
            case '4':
                $where_city = ' and provinceid='.$medata['provinceid'];
                $iscs = false;
                $response['position'] = 4;
                break;
            // 城市经理
            case '3':
                $where_city = ' and pid='.$medata['id'];
                $iscs = true;
                $response['position'] = 3;
                break;
            default:
                exit(json_encode(['error'=>'1','message'=>'您的账号权限不涉及本功能！']));
        }
        $BeginDate = strtotime(date("Y-m-01"));
        $EndDate = mktime(0, 0, 0,date('m')+1,1)-1;
        // $Beginday = strtotime(date('Y-m-01 00:00:00'));
        // $EndDay = strtotime(date('Y-m-15 23:59:59'));
        $Beginday = strtotime($geta['starttime'].' 00:00:00');
        $EndDay   = strtotime($geta['endtime'].' 23:59:59');
        $timesql = '';
        $time_reg_sql =  ' and create_time>='.$Beginday.' and create_time<='.$EndDay;
        // 所有城市经理
        $manager = $admin_mdb->where('position=3'.$where_city.$this->istest['testsql'])->select();
        $channelcount = [];
        // 城市经理总数
        $cscount = count($manager);
        $gwcount = 0;
        $data = [];
        // 渠道状态
        $status_arr = ['0'=>'等待城市经理审核','1'=>'等待风控审核','2'=>'已激活','3'=>'关闭状态（已通过审核）','-1'=>'被拒绝','-2'=>'被驳回（正在修改）'];
        for ($i=0; $i <  $cscount; $i++) { 
            // 城市经理下属的所有GW
            $Gwdata = $admin_mdb->where('pid='.$manager[$i]['id'].' and position=1'.$this->istest['testsql'])->field('id,name,tel')->select();
            $manager_sheet_title[] = $manager[$i]['name'];
            $data[$i] = [];
            $manager_regt = 0;
            $manager_regt_day = 0;
            // 统计渠道数据
            if(!$Gwdata){
                $manager[$i]['channelcount'] = 0;
                $manager[$i]['channel_create_count'] = 0;
                $manager[$i]['channel_success_count'] = 0;
                $manager[$i]['channel_refuse_count'] = 0;
                $manager[$i]['channel_back_count'] = 0;
                $manager[$i]['GWcount'] = 0;
                $manager[$i]['GWlist'] = [];
            }else{
                $manager[$i]['GWcount'] = count($Gwdata);
                $Gwdataids = implode(',',array_column($Gwdata,'id'));
                $manager[$i]['channelcount'] = $wayuser_mdb->where('admin_id in('.$Gwdataids.')'.$timesql)->count();
                $data[$i][] = ['顾问姓名','','','','',''];
                for ($s=0; $s < count($Gwdata); $s++) { 
                    $Gwdata[$s]['channel_count'] = $wayuser_mdb->where('admin_id='.$Gwdata[$s]['id'].$timesql)->count();
                    $manager[$i]['channel_create_count'] += $Gwdata[$s]['channel_create_count'] = $wayuser_mdb->where('status>=0 and status<=1 and admin_id='.$Gwdata[$s]['id'].$timesql)->count();
                    $manager[$i]['channel_success_count'] += $Gwdata[$s]['channel_success_count'] = $wayuser_mdb->where('status=2 and admin_id='.$Gwdata[$s]['id'].$timesql)->count();
                    $manager[$i]['channel_refuse_count'] += $Gwdata[$s]['channel_refuse_count'] = $wayuser_mdb->where('status=-1 and admin_id='.$Gwdata[$s]['id'].$timesql)->count();
                    $manager[$i]['channel_back_count'] += $Gwdata[$s]['channel_back_count'] = $wayuser_mdb->where('status=-2 and admin_id='.$Gwdata[$s]['id'].$timesql)->count();
                    $gw_regt = 0;
                    $gw_regt_day = 0;
                    $waydata = $wayuser_mdb->where('admin_id='.$Gwdata[$s]['id'].$timesql)->select();

                    for ($j=0; $j < $Gwdata[$s]['channel_count']; $j++) { 
                        $channel_regt = $user_mdb->where('waynum='.$waydata[$j]['waynum'].' and waynum<>0')->count();
                        $gw_regt += $channel_regt;
                        $channel_regt_day = $user_mdb->where('waynum='.$waydata[$j]['waynum'].' and waynum<>0'.$time_reg_sql)->count();
                        $gw_regt_day += $channel_regt_day;
                    }
                    $manager_regt_day += $gw_regt_day;
                    $data[$i][] = [$Gwdata[$s]['name'], '姓名', '手机号', '推荐码', '状态', '注册用户总数', $geta['starttime'].'/'.$geta['endtime'].'增长用户数'];
                    $data[$i][] = ['按顾问“'.$Gwdata[$s]['name'].'”统计', '注册用户总数：'.$gw_regt, $geta['starttime'].'/'.$geta['endtime'].'增长用户数：'.$gw_regt_day, '', '', '', ];
                    $data[$i][] = ['','','','','',''];

                    for ($j=0; $j < $Gwdata[$s]['channel_count']; $j++) { 
                        $manager_regt += $channel_regt = $user_mdb->where('waynum='.$waydata[$j]['waynum'].' and waynum<>0')->count();
                        $channel_regt_day = $user_mdb->where('waynum='.$waydata[$j]['waynum'].' and waynum<>0'.$time_reg_sql)->count();
                        $data[$i][] = [
                            $Gwdata[$s]['name'],
                            $waydata[$j]['name'],
                            $waydata[$j]['tel'],
                            $waydata[$j]['waynum'],
                            $status_arr[$waydata[$j]['status']],
                            $channel_regt,
                            $channel_regt_day,
                        ];
                    }
                    
                    
                }
            }
            $manager_title[$i] = ['按城市经理统计',
              '注册用户数：'.$manager_regt,
              $geta['starttime'].'/'.$geta['endtime'].'增长用户数：'.$manager_regt_day,
              '',
              '',
              ''
            ];
        }
        $filename_n = $geta['starttime'].$geta['endtime'];
        $filename = ExportExcelTable($manager_title,$data,$manager_sheet_title,$filename_n,'./Public/Uploads/ChannelCount/');
        exit(json_encode(['success'=>'OK','filename'=>$filename_n,'message'=>$filename_n.'导出成功！']));
    }

    public function riskChaennleExcel(){
        $admin_mdb =  M('admin');
        $wayuser   =  M('wayuser');
        $user_mdb =  M('user');
        $city_db = D('city');
        $provinceid = I('get.');
        if($provinceid['province'] != ""){
           $addssre = $admin_mdb->where("test_sign = 0 and position = 3 and provinceid = ".$provinceid['province'])->select();
        }else{
           $addssre = $admin_mdb->where("test_sign = 0 and position = 3")->select();
        }
        $startTime = I('request.startTime');
        $endTime = I('request.endTime');
        $timesql = '';
        if ($startTime && $endTime) {
            $timesql = ' and create_time >='.$startTime.' and create_time <='.$endTime;
        }
        $Gwdataids = implode(',',array_column($addssre,'id'));
        $GWadmin = $admin_mdb->where('test_sign = 0 and pid in ('.$Gwdataids.')')->select();
        $datas       = [];
        $count       = 0;//渠道总数合计
        $counttoo    = 0;//渠道通过数合计
        $countno     = 0;//渠道被拒数合计
        $countreject = 0;//渠道驳回数合计
        $countcs     = 0;//等待城市经理审合计
        $countfk     = 0;//等待风控审合计
        foreach ($GWadmin as $key => $value) {
            $csname  = $admin_mdb->where(array('id = '.$value['pid']))->find()['name'];
            $admincount = $wayuser->where("admin_id =".$value['id'].$timesql)->count();
            $admintoo   = $wayuser->where("status = 2 and admin_id =".$value['id'].$timesql)->count();
            $adminno    = $wayuser->where("status = -1 and admin_id =".$value['id'].$timesql)->count();
            $adminreject= $wayuser->where("status = -2 and admin_id =".$value['id'].$timesql)->count();
            $admincs    = $wayuser->where("status = 0 and admin_id =".$value['id'].$timesql)->count();
            $adminfk    = $wayuser->where("status = 1 and admin_id =".$value['id'].$timesql)->count();
            $data       = array();
            $data[]        = $value['name'];
            $data[]        = $csname;
            $count        += $admincount; 
            $data[]        = $admincount;
            $counttoo     += $admintoo;
            $data[]        = $admintoo;
            $countno      += $adminno;
            $data[]        = $adminno;
            $countreject  += $adminreject;
            $data[]        = $adminreject;
            $countcs      += $admincs;
            $data[]        = $admincs;
            $countfk      += $countfk;
            $data[]        = $countfk;
            $datas[] = $data;
        }
        $datas[] = array("合计","",$count,$counttoo,$countno,$countreject,$countcs,$countfk);
        $filename_n = date('YmdHis');
        $title   = ['GW姓名','所属城市经理','渠道总数','渠道通过数','渠道被拒数','渠道驳回数','等待城市经理审核数','等待风控审核数'];
        $filename_exce = "sheet";
        $filename = ExportExcelTable($title,$datas,$filename_exce,$filename_n,'./Public/Uploads/ChannelCount/');
        exit(json_encode(['success'=>'OK','filename'=>$filename_n,'message'=>$filename_n.'导出成功！']));
        // print_r($datas);die;
    }
}